////////////////////////////////////////////////////////////////////////////////
// Copyright (c) 2011-2012 www.iflytek.com. All Rights Reserved.
//  This software for customer relationship management system, developed by Noo team.
//  Software code and design for the team, copy rights reserved.
////////////////////////////////////////////////////////////////////////////////

package com.iflytek.ci.common.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

import com.google.common.collect.Lists;
import com.iflytek.ci.common.exceptions.UncheckedException;
import com.iflytek.ci.main.utils.Dates;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * <p>
 * Excel工具类，暂时不支持Excel 2007.
 * </p>
 *
 * @author poplar.yfyang
 * @version 1.0 2012-11-18 11:23 AM
 * @since JDK 1.5
 */
@SuppressWarnings("UnusedDeclaration")
public class Excels {

	/** static Workbook */
	private static Workbook rwb;

	/**
	 * 根据传入的inputStream流创建Workbook对象
	 *
	 * @param inputStream 文件流
	 * @return excel
	 */
	public static Workbook getWorkbook(InputStream inputStream) {
		try {
			rwb = Workbook.getWorkbook(inputStream);
		} catch (BiffException e) {
			throw new UncheckedException("格式错误", e);
		} catch (IOException e) {
			throw new UncheckedException(e);
		}
		return rwb;
	}

	/**
	 * 读取指定sheet的数据，返回FormatContentParser的getResult结果。
	 * 此方法用于读取一个Excel文件每个sheet的格式不同的。
	 *
	 * @param sheetNumber 读取第几个sheet
	 * @param parser      Excel内容解析器
	 * @param fileName    待解析的Excel全路径文件名
	 */
	public static Object readExcel(int sheetNumber, FormatContentParser parser, String fileName) {
		FileInputStream inputStream = getFileInputStream(fileName);
		return Excels.readExcelFromStream(sheetNumber, parser, inputStream);
	}

	/**
	 * 读取指定sheet的数据，返回FormatContentParser的getResult结果。
	 * 此方法用于读取一个Excel文件每个sheet的格式不同的。
	 *
	 * @param sheetNumber 读取第几个sheet
	 * @param parser      Excel内容解析器
	 * @param inputStream 待解析的Excel文件流
	 */
	public static Object readExcelFromStream(int sheetNumber, FormatContentParser parser,
											 FileInputStream inputStream) {
		rwb = getWorkbook(inputStream);
		Sheet rs = rwb.getSheet(sheetNumber);
		readSheet(parser, rs);
		return parser.getResult();
	}

	/**
	 * 用于解析所有sheet都一样的Excel文件。 解析整个Excel文件，并将每一个sheet的内容放到加入到返回集中，
	 * 返回的结果集有几个sheet就会有几个FormatContentParser的对象。
	 *
	 * @param parser   Excel内容解析器
	 * @param fileName 待解析的Excel全路径文件名
	 */
	public static Object[] readExcel(FormatContentParser parser, String fileName) {
		FileInputStream inputStream = getFileInputStream(fileName);
		return Excels.readExcelFromStream(parser, inputStream);
	}

	/**
	 * 用于解析所有sheet都一样的Excel文件。 解析整个Excel文件，并将每一个sheet的内容放到加入到返回集中，
	 * 返回的结果集有几个sheet就会有几个FormatContentParser的对象。
	 *
	 * @param parser      Excel内容解析器
	 * @param inputStream 待解析的Excel全文件流
	 */
	public static Object[] readExcelFromStream(FormatContentParser parser,
											   FileInputStream inputStream) {
		rwb = getWorkbook(inputStream);
		// 获得总 Sheets
		Sheet[] sheets = rwb.getSheets();
		int sheetLen = sheets.length;
		Object[] object = new Object[sheetLen];
		for (int i = 0; i < sheetLen; i++) {
			// 获得单个Sheets 含有的行数
			Sheet rs = rwb.getSheet(i);
			readSheet(parser, rs);
			object[i] = parser.getResult();
		}
		return object;
	}

	/**
	 * 用于解析各sheet格式不同的Excel文件，要求依次传入每个sheet的解析器
	 * 注意：对于其中又有某些sheet格式相同的情况下，可以采用parser引用的方 式，不需要创建多个相同的parser。
	 * 解析整个Excel文件，并将每一个sheet的内容放到加入到返回集中，
	 * 返回的结果集有几个sheet就会有几个FormatContentParser的对象。
	 *
	 * @param parsers  Excel内容解析器
	 * @param fileName 待解析的Excel全路径文件名
	 */
	public static Object[] readExcel(FormatContentParser[] parsers, String fileName) {
		FileInputStream inputStream = getFileInputStream(fileName);
		return Excels.readExcelFromStream(parsers, inputStream);
	}

	/**
	 * 用于解析各sheet格式不同的Excel文件，要求依次传入每个sheet的解析器，注意：对于其中又有某些sheet格式相同的情况下，
	 * 可以采用parser引用的方 式，不需要创建多个相同的parser。 解析整个Excel文件，并将每一个sheet的内容放到加入到返回集中，
	 * 返回的结果集有几个sheet就会有几个FormatContentParser的对象。
	 *
	 * @param parsers     Excel内容解析器
	 * @param inputStream 待解析的Excel文件流
	 */
	public static Object[] readExcelFromStream(FormatContentParser[] parsers,
											   FileInputStream inputStream) {
		Object[] object = new Object[parsers.length];
		for (int i = 0; i < parsers.length; i++) {
			object[i] = Excels.readExcelFromStream(parsers[i], inputStream);
		}
		return object;
	}

	/**
	 * 根据传入的sheet的解析器和sheet对象，读取excel
	 *
	 * @param parser 内容解析
	 * @param rs     excel sheet
	 */
	private static void readSheet(FormatContentParser parser, Sheet rs) {
		int row = rs.getRows();// 得到该sheet的行数
		int line = rs.getColumns();// 得到该sheet的列数
		for (int j = 0; j < row; j++) {
			parser.newLine(j);
			for (int k = 0; k < line; k++) {
				// 日期格式的读取
				if (rs.getCell(k, j).getType() == CellType.DATE) {
					DateCell content = (DateCell) rs.getCell(k, j);
					// String content = rs.getCell(k, j).getContents();
					parser.parseUnit(k, j, Dates.dateToString(content.getDate(),
							4));
				} else {
					String content = rs.getCell(k, j).getContents();
					parser.parseUnit(k, j, content);
				}
			}
		}
	}

	/**
	 * 根据传入的fileName生成FileInputStream流
	 *
	 * @param fileName excel文件的全路径
	 * @return 文件流
	 */
	private static FileInputStream getFileInputStream(String fileName) {
		FileInputStream inputStream;
		try {
			inputStream = new FileInputStream(fileName);
		} catch (FileNotFoundException e) {
			throw new UncheckedException("找不到 " + fileName + "文件", e);
		}
		return inputStream;
	}

	/**
	 * 读取Excel文件的第0个sheet的数据，并将数据存放在List中，其中每行的数据又是一个List（依次存放每列数据），
	 * 注意：所有单元格的数据都是String类型。
	 *
	 * @param filePath 文件地址
	 * @return List   文件内容
	 */
	public static List readExcel(String filePath) {
		return (List) Excels.readExcel(0, new FormatContentParserImpl(), filePath);
	}

	/**
	 * 读取Excel文件的第0个sheet的数据，并将数据存放在List中，其中每行的数据又是一个List（依次存放每列数据），
	 * 注意：所有单元格的数据都是String类型。
	 *
	 * @param inputStream 文件流
	 * @return List
	 */
	public static List readExcel(FileInputStream inputStream) {
		return (List) Excels.readExcelFromStream(0, new FormatContentParserImpl(),
				inputStream);
	}

	/**
	 * 写excel文件
	 *
	 * @param list      <string[]>
	 * @param fileName  文件名
	 * @param titleName 标题
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void writeExcel(List list, String fileName, String titleName)
			throws IOException, WriteException {
		WritableWorkbook book = null;
		try {
			File file = new File(fileName);
			// 得到一个可以写的book对象
			book = Workbook.createWorkbook(file);

			// 写一个sheet
			WritableSheet sheet = book.createSheet(titleName, 0);
			// 字体
			WritableFont fontHead = new WritableFont(WritableFont.TAHOMA, 15, WritableFont.BOLD);
			// 标题居中
			WritableCellFormat formatHead = new WritableCellFormat(fontHead);
			formatHead.setAlignment(jxl.format.Alignment.CENTRE);
			int col_span = 1;
			if (list != null && list.size() > 0) {
				// 取出excel的列数以便title跨列
				col_span = ((String[]) list.get(0)).length;
			}
			Label labTitle = new Label(0, 0, titleName, formatHead);
			// 写标题
			sheet.addCell(labTitle);
			// 从(0,0)到(col_span-1,0)单元格合并
			sheet.mergeCells(0, 0, col_span - 1, 0);
			for (int i = 0; i < (list != null ? list.size() : 0); i++) {
				String[] rowArray = (String[]) list.get(i);
				for (int j = 0; j < rowArray.length; j++) {
					// j:列，i:行，rowArray[j]:value
					Label labValue = new Label(j, i + 1, rowArray[j]);
					sheet.addCell(labValue);
				}
			}
			book.write();
		} finally {
			if (book != null) {
				book.close();
			}
		}

	}

	/**
	 * 写excel文件
	 *
	 * @param list      <string[]>
	 * @param fileName  文件名
	 * @param titleName 标题
	 * @throws IOException
	 * @throws WriteException
	 */
	public static void writeExcel(HttpServletResponse response, List list,
								  String fileName, String titleName)
			throws IOException, WriteException {
		WritableWorkbook book = null;
		try {
			OutputStream os = response.getOutputStream();
			book = Workbook.createWorkbook(os);

			response.setContentType("application/x-msdownload;");
			response.setHeader("Content-disposition", "attachment; filename="
					+ (new String(fileName.getBytes("GBK"), "ISO-8859-1")) + ".xls");
			response.setContentType("application/ms-excel;");
			// 写一个sheet
			WritableSheet sheet = book.createSheet(titleName, 0);
			WritableFont fontHead = new WritableFont(WritableFont.TAHOMA, 15,
					WritableFont.BOLD);// 字体
			WritableCellFormat formatHead = new WritableCellFormat(fontHead);
			// 标题居中
			formatHead.setAlignment(jxl.format.Alignment.CENTRE);
			int col_span = 1;
			if (list != null && list.size() > 0) {
				// 取出excel的列数以便title跨列
				col_span = ((String[]) list.get(0)).length;
			}
			Label labTitle = new Label(0, 0, titleName, formatHead);
			// 写标题
			sheet.addCell(labTitle);
			// 从(0,0)到(col_span-1,0)单元格合并
			sheet.mergeCells(0, 0, col_span - 1, 0);
			for (int i = 0; i < (list != null ? list.size() : 0); i++) {
				String[] rowArray = (String[]) list.get(i);
				for (int j = 0; j < rowArray.length; j++) {
					Label labValue = new Label(j, i + 1, rowArray[j]);// j:列，i:行，rowArray[j]:value
					sheet.addCell(labValue);
				}
			}
			book.write();
		} finally {
			if (book != null) {
				book.close();
			}
		}
	}

	/**
	 * 写excel文件
	 *
	 * @param response 输出流
	 * @param list     <string[]>
	 * @param fileName 文件名
	 * @throws IOException
	 * @throws WriteException
	 */
	public static void writeExcel(HttpServletResponse response, List list, String fileName)
			throws IOException, WriteException {
		WritableWorkbook book = null;
		try {

			OutputStream os = response.getOutputStream();
			book = Workbook.createWorkbook(os);

			response.setContentType("application/x-msdownload;");
			response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("GBK"), "ISO-8859-1")) + ".xls");
			response.setContentType("application/ms-excel;");
			// 写一个sheet
			WritableSheet sheet = book.createSheet(fileName.substring(0, 10), 0);
			// 字体
			WritableFont fontHead = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD);
			WritableCellFormat formatHead = new WritableCellFormat(fontHead);

			// 写标题
			if (list != null && list.size() > 0) {
				String[] title = (String[]) list.get(0);
				for (int n = 0; n < title.length; n++) {
					Label labTitle = new Label(n, 0, title[n], formatHead);
					sheet.setColumnView(n, 23);
					sheet.addCell(labTitle);
				}
			}
			// 写内容
			for (int i = 1; i < (list != null ? list.size() : 0); i++) {
				String[] rowArray = (String[]) list.get(i);
				for (int j = 0; j < rowArray.length; j++) {
					// j:列，i:行，rowArray[j]:value
					Label labValue = new Label(j, i, rowArray[j]);
					sheet.addCell(labValue);
				}
			}
			book.write();
		} finally {
			if (book != null) {
				book.close();
			}
		}
	}

	/**
	 * 写excel文件
	 *
	 * @param list      [] 列表数组<string[]>
	 * @param fileName  文件名
	 * @param titleName [] 标题
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void writeExcel(HttpServletResponse response, List[] list,
								  String fileName, String[] titleName)
			throws IOException, WriteException {
		WritableWorkbook book = null;
		try {
			OutputStream os = response.getOutputStream();
			book = Workbook.createWorkbook(os);

			response.setContentType("application/x-msdownload;");
			response.setHeader("Content-Disposition", "attachment; filename=\"" + java.net.URLEncoder.encode(fileName, "UTF-8") + ".xls\"");

			WritableSheet[] sheets = new WritableSheet[list.length];
			for (int i = 0; i < list.length; i++) {
				// 写一个sheet
				sheets[i] = book.createSheet(titleName[i], i);
				// 字体
				WritableFont fontHead = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.BOLD);
				WritableCellFormat formatHead = new WritableCellFormat(fontHead);
				// 标题居中
				formatHead.setAlignment(jxl.format.Alignment.CENTRE);

				for (int j = 0; j < list[i].size(); j++) {
					String[] rowArray = (String[]) list[i].get(j);
					for (int k = 0; k < rowArray.length; k++) {
						Label labValue;
						if (j == 0) {
							// k:列，j:行，rowArray[k]:value
							labValue = new Label(k, j, rowArray[k], formatHead);
						} else {
							// k:列，j:行，rowArray[k]:value
							labValue = new Label(k, j, rowArray[k]);
						}
						sheets[i].addCell(labValue);
					}
				}
			}
			book.write();
		} finally {
			if (book != null) {
				book.close();
			}
		}
	}

	/**
	 * 写excel文件 (安联救援邮件附件)
	 *
	 * @param list      <string[]> Excel表格内容
	 * @param fileName  路径+文件名
	 * @param titleName 工作表标签
	 * @throws IOException
	 * @throws WriteException
	 */
	public static WritableWorkbook writeExcelHelp(List list, String fileName,
												  String titleName)
			throws IOException, WriteException {
		WritableWorkbook book = null;
		try {
			File file = new File(fileName);
			// 得到一个可以写的book对象
			book = Workbook.createWorkbook(file);
			// 写一个sheet
			WritableSheet sheet = book.createSheet(titleName, 0);
			//标题格式
			WritableFont fontHead = new WritableFont(WritableFont.TAHOMA, 12,
					WritableFont.BOLD);
			WritableCellFormat formatHead = new WritableCellFormat(fontHead);
			// 标题居中
			formatHead.setAlignment(jxl.format.Alignment.CENTRE);

			//字段值格式
			WritableFont valueFont = new WritableFont(WritableFont.TAHOMA, 10);
			// 标题居中
			WritableCellFormat valueFormat = new WritableCellFormat(valueFont);
			valueFormat.setAlignment(jxl.format.Alignment.CENTRE);
			if (list != null && list.size() > 0) {
				String[] titleArray = (String[]) list.get(0);
				int title_len = titleArray.length;

				for (int j = 0; j < title_len; j++) {
					String title = titleArray[j];
					Label labTitle = new Label(j, 0, title, formatHead);
					sheet.setColumnView(j, 18);
					sheet.addCell(labTitle);
				}

				String[] valueArray = (String[]) list.get(1);
				int value_len = valueArray.length;

				for (int k = 0; k < value_len; k++) {
					String value = valueArray[k];
					Label labValue = new Label(k, 1, value, valueFormat);
					sheet.addCell(labValue);
				}
			}
			book.write();
			return book;
		} finally {
			if (book != null) book.close();
		}
	}

	/**
	 * 写excel文件
	 *
	 * @param response     输出
	 * @param list         <string[]> Excel表格内容
	 * @param fileName     路径+文件名
	 * @param titleName    工作表标签
	 * @param excelDataRow [] 标题
	 * @throws IOException
	 * @throws WriteException
	 */
	public static void writeExcel(HttpServletResponse response, List<String[]> list,
								  String fileName, String titleName, String[] excelDataRow, int[] cellSize)
			throws IOException, WriteException {
		WritableWorkbook book = null;
		try {
			OutputStream os = response.getOutputStream();
			book = Workbook.createWorkbook(os);

			response.setContentType("application/x-msdownload;");
			response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("GBK"), "ISO-8859-1")) + ".xls");
			response.setContentType("application/ms-excel;");
			// 写一个sheet
			WritableSheet sheet = book.createSheet(titleName, 0);
			// 字体
			WritableFont fontHead = new WritableFont(WritableFont.TAHOMA, 15, WritableFont.BOLD);
			WritableCellFormat formatHead = new WritableCellFormat(fontHead);
			// 标题居中
			formatHead.setAlignment(jxl.format.Alignment.CENTRE);
			// 取出excel的列数以便title跨列
			int col_span = excelDataRow.length;

			Label labTitle = new Label(0, 0, titleName, formatHead);
			// 写标题
			sheet.addCell(labTitle);
			// 从(0,0)到(col_span-1,0)单元格合并
			sheet.mergeCells(0, 0, col_span - 1, 0);
			/**
			 * 设置单元格每列的大小
			 */
			for (int i = 0; i < cellSize.length; i++) {
				sheet.setColumnView(i, cellSize[i]);
			}


			for (int j = 0; j < excelDataRow.length; j++) {
				// j:列，第一行，excelDataRow[j]:value
				Label labValue = new Label(j, 1, excelDataRow[j]);
				sheet.addCell(labValue);
			}
			for (int i = 0; i < list.size(); i++) {
				String[] objectArray = list.get(i);
				for (int j = 0; j < objectArray.length; j++) {
					// j:列，i:行，objectArray[j]:value
					Label labValue = new Label(j, i + 2, objectArray[j]);
					sheet.addCell(labValue);
				}
			}
			book.write();
		} finally {
			if (book != null) {
				book.close();
			}
		}
	}

	/** 提供一种基本的实现类，可直接调用 */
	public static class FormatContentParserImpl implements FormatContentParser {
		private final List<List<String>> list = Lists.newArrayList();
		private List<String> tempList;

		public Object getResult() {
			return list;
		}

		public void parseUnit(int x, int y, String content) {
			tempList.add(content);
		}

		public void newLine(int lineNumber) {
			tempList = Lists.newArrayList();
			list.add(tempList);
		}

	}
}
